﻿CREATE PROCEDURE [dbo].[spCalcOrderTotals]
	@OrderID int
AS
	-- Set the ProductTotal
	declare @ProductTotal money
	select @ProductTotal = coalesce(SUM(round(Qty * UnitPrice * (1 - Discount), 2)), 0) from tblOrderDetail where OrderID = @OrderID

	update tblOrderHeader set ProductTotal = @ProductTotal
	where OrderID = @OrderID

	-- If the shipping is not yet set then set the shipping amount

	update tblOrderHeader set Shipping = 0 where Shipping is null and OrderID = @OrderID

	-- Calculate the taxes
	delete from tblOrderTax where OrderID = @OrderID

	insert into tblOrderTax ( OrderID, TaxRateID, Amount )
	select @OrderID, T6.TaxRateID, dbo.CalcTax(T6.TaxRateID, @ProductTotal) 
			+ case when T6.TaxShipping = 1 then dbo.CalcTax(T6.TaxRateID, T1.Shipping) else 0 end
	from tblOrderHeader T1
		join tblAddress T2 on T1.ShipTo_AddressID = T2.AddressID
		join tblPostalCode T3 on T2.PostalCode = T3.PostalCode 
			and T2.CountryID = T3.CountryID
		join tblPostalCodeTaxZone T4 on T3.PostalCodeID = T4.PostalCodeID
		join tblTaxZone T5 on T4.TaxZoneID = T5.TaxZoneID
		join tblTaxRate T6 on T5.TaxZoneID = T6.TaxRateID
	where T1.OrderID = @OrderID

	update T1 set Taxes = T2.Taxes
	from tblOrderHeader T1
		join ( select OrderID, SUM( Amount ) as Taxes
			from tblOrderTax
			group by OrderID ) as T2 on T1.OrderID = T2.OrderID
	where T1.OrderID = @OrderID

	update tblOrderHeader set OrderTotal = ProductTotal + Shipping + Taxes
	where OrderID = @OrderID

RETURN 0